﻿using DHQGHN.DAL.Base;
using DHQGHN.DAL.Models.DHQG;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DHQGHN.DAL.DAO.DHQG
{
    public class CommentDAO : SqlDAO
    {
        public const string PROCEDURE_INSERT = "spCOMMENT_Insert";
        public const string PROCEDURE_GET_ALL_BY_BAN = "spComment_GetGetAll_BY_BAN";
        public const string PROCEDURE_GET_ALL_BY_DV = "spComment_GetGetAll_BY_DV";
        public const string PROCEDURE_GET_ALL = "spComment_GetGetAll";
        public const string PROCEDURE_Update_BY_KHTC = "spComment_Update_BY_KHTC";
        public const string PROCEDURE_Update_BY_KHTC_BAN = "spComment_Update_BY_KHTC_BAN";
        public const string PROCEDURE_DELETE_COMMENT_BAN = "spComment_DELETE_BY_NHOMCT_NHOMDV";
        public const string PROCEDURE_UPDATE_BY_DV = "spComment_Update_BY_DV";
        public const string PROCEDURE_CHECKCHOT = "spComment_CheckChot";
        public const string PROCEDURE_HUY_CHOT = "spComment_Huy_Chot";
        public const string PROCEDURE_GET_ALL_GIUANAM_BY_DV = "spComment_GetGetAll_GIUANAM_BY_DV";
        public const string PROCEDURE_UPDATE_GIUANAM_BY_DV = "spComment_Update_GIUANAM_BY_DV";
        public const string PROCEDURE_INSERT_GIUANAM_BY_BAN = "spCOMMENT_Insert_BAN";
        public const string PROCEDURE_GET_DV_GIUANAM = "spComment_GET_DV_GIUANAM";
        public const string PROCEDURE_CheckBanchuaGui = "spComment_CheckBanchuaGui";

        public const string PROCEDURE_CHOT = "spComment_Chot";
        public const string PROCEDURE_UPDATE_BY_BAN = "spComment_Update_BY_BAN";
        public const string PROCEDURE_UPDATE_Approve = "spComment_Update_Approve";
        public const string PROCEDURE_UPDATE_Approve_GiuaNam = "spComment_Update_Approve_GiuaNam";

        public const string PARAM_TABLE = "@Table";
        public const string PARAM_BAN_ID = "@BAN_ID";

        public const string PARAM_NAM_KE_HOACH_ID = "@NAM_KE_HOACH_ID";
        public const string PARAM_NGUOI_DUNG_ID = "@NGUOI_DUNG_ID";
        public const string PARAM_MOC = "@MOC";
        public const string PARAM_NHOM_DON_VI_ID = "@NHOM_DON_VI_ID";
        public const string PARAM_NHOM_CHI_TIEU = "@NHOM_NHOM_CHI_TIEU";
        public const string PARAM_FLAG = "@FLAG";
        public const string PARAM_CHI_TIEU_ID = "@CHI_TIEU_ID";
        public const string PARAM_DON_VI_ID = "@DON_VI_ID";
        public const string PARAM_GIA_TRI = "@GIA_TRI";
        public const string PARAM_COMMENT_DV = "@COMMENT_DV";
        public const string PARAM_COMMENT_BAN = "@COMMENT_BAN";
        public const string PARAM_IS_ACTIVE = "@IS_ACTIVE";
        public const string PARAM_STATUS_ID = "@STATUS_ID";
        public const string PARAM_MOC_KIEM_SOAT_ID = "@MOC_KIEM_SOAT_ID";
        public const string PARAM_MOC_KIEM_SOAT_ID_BEFO = "@MOC_KIEM_SOAT_ID_BEFO";
        public CommentDAO(string connectionString, System.Type type) :
            base(connectionString, type) { }

        public bool Save(List<Comment> data)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT);
                DataTable dataTable = GetDataTable(data);
                cmd.Parameters.Add(new SqlParameter(PARAM_TABLE, dataTable));
                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }
        public int DeleteCommentbyBan(int nhomdvid, int nhomct, int namkh, int ban, int moc, int flag)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE_COMMENT_BAN);
            cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdvid));
            cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_CHI_TIEU, nhomct));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, ban));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public List<CheckChot> GetlistCommnetBanchuachot(int namkh, int moc)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CheckBanchuaGui);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            return GetList<CheckChot>(cmd);
        }
        public int CommentChot(int namkh, int ban, int moc)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHOT);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, ban));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, moc));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public List<Comment> GetallbyDonviGiuaNam(int namkh, int moc)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DV_GIUANAM);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            return GetList<Comment>(cmd);
        }
        public List<Comment> GetallComment(int namkh, int moc, int statusid)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
            return GetList<Comment>(cmd);
        }
        public List<Comment> GetallByBan(int banid, int namkh, int moc, int flag, int mocks)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL_BY_BAN);
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, banid));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
            return GetList<Comment>(cmd);
        }
        public List<Comment> GetallGiuanamByDV(int donviid, int namkh, int moc)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL_GIUANAM_BY_DV);
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            return GetList<Comment>(cmd);
        }
        public int UpdateCommentGiuaNambyDV(Comment cmt)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_UPDATE_GIUANAM_BY_DV);
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cmt.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cmt.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cmt.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cmt.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, cmt.GIA_TRI));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_DV, cmt.COMMENT_DV));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int InsertGiuanamByBan(Comment cmt)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT_GIUANAM_BY_BAN);
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, cmt.BAN_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cmt.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_BAN, cmt.COMMENT_BAN));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cmt.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cmt.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, cmt.GIA_TRI));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_DV, cmt.COMMENT_DV));
            cmd.Parameters.Add(new SqlParameter(PARAM_IS_ACTIVE, cmt.IS_ACTIVE));
            cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, cmt.STATUS_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cmt.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, cmt.MOC_KIEM_SOAT_ID));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public List<Comment_DV> GetallByDv(int dvid, int namkh, int moc, int flag, int mocks, int nguoidungid)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL_BY_DV);
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, dvid));
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
            cmd.Parameters.Add(new SqlParameter(PARAM_NGUOI_DUNG_ID, nguoidungid));
            return GetList<Comment_DV>(cmd);
        }
        public List<CheckChot> CommentCheckchot(int namkh, int moc, int ban, int mocks)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHECKCHOT);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, ban));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
            return GetList<CheckChot>(cmd);
        }
        public int CommentHuyCHot(int namkh, int ban, int mocks, int mocbefo)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_HUY_CHOT);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, ban));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID_BEFO, mocbefo));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateComment(Comment cm)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_UPDATE_BY_DV);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cm.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cm.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cm.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, cm.GIA_TRI));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_DV, cm.COMMENT_DV));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cm.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, cm.MOC_KIEM_SOAT_ID));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateCommentbyKHTC(Comment cm)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Update_BY_KHTC);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cm.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cm.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cm.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_DV, cm.COMMENT_DV));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cm.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, cm.STATUS_ID));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateCommentbyKHTC_BAN(Comment cm)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Update_BY_KHTC_BAN);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cm.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cm.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cm.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_BAN, cm.COMMENT_BAN));
            cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, cm.GIA_TRI));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cm.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, cm.STATUS_ID));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateCommentApprove(int namkh, int chitieu, int donvi, int moc, int mocks)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_UPDATE_Approve);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateCommentApproveGiuaNam(int namkh, int chitieu, int donvi, int moc, int flag)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_UPDATE_Approve_GiuaNam);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, moc));
            cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        public int UpdateCommentByBAN(Comment cm)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_UPDATE_BY_BAN);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, cm.NAM_KE_HOACH_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, cm.CHI_TIEU_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, cm.DON_VI_ID));
            cmd.Parameters.Add(new SqlParameter(PARAM_COMMENT_BAN, cm.COMMENT_BAN));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC, cm.MOC));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, cm.MOC_KIEM_SOAT_ID));
            int result = ExecuteNonQuery(cmd);
            return result;
        }
        private DataTable GetDataTable(List<Comment> data)
        {
            DataTable table = new DataTable();
            table.Columns.Add("COMMENT_ID");
            table.Columns.Add("BAN_ID");
            table.Columns.Add("CHI_TIEU_ID");
            table.Columns.Add("COMMENT_BAN");
            table.Columns.Add("DON_VI_ID");
            table.Columns.Add("GIA_TRI");
            table.Columns.Add("COMMENT_DV");
            table.Columns.Add("STATUS_ID");
            table.Columns.Add("IS_ACTIVE");
            table.Columns.Add("MOC");
            table.Columns.Add("NAM_KE_HOACH_ID");
            table.Columns.Add("MOC_KIEM_SOAT_ID");

            foreach (var item in data)
            {
                table.Rows.Add(item.COMMENT_ID,
                               item.BAN_ID,
                               item.CHI_TIEU_ID,
                               item.COMMENT_BAN,
                               item.DON_VI_ID,
                               item.GIA_TRI,
                               item.COMMENT_DV,
                               item.STATUS_ID,
                               item.IS_ACTIVE,
                               item.MOC,
                               item.NAM_KE_HOACH_ID
                               , item.MOC_KIEM_SOAT_ID);
            }

            return table;
        }
    }
}
